{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "b51ff845",
   "metadata": {},
   "source": [
    "# Consultor SQL OpenAI y Langchain\n",
    "\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "<img src=\"https://raw.githubusercontent.com/YonatanRA/webinar_text2sql/refs/heads/main/images/text-to-sql.webp\" style=\"width:400px;\"/>\n",
    "\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "<img src=\"https://raw.githubusercontent.com/YonatanRA/webinar_text2sql/refs/heads/main/images/sql_agent.png\" style=\"width:800px;\"/>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "797551c5",
   "metadata": {
    "toc": true
   },
   "source": [
    "<h1>Tabla de Contenidos<span class=\"tocSkip\"></span></h1>\n",
    "<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#1---OpenAI-Api-Key\" data-toc-modified-id=\"1---OpenAI-Api-Key-1\">1 - OpenAI Api Key</a></span></li><li><span><a href=\"#2---Conexión-a-SQL\" data-toc-modified-id=\"2---Conexión-a-SQL-2\">2 - Conexión a SQL</a></span></li><li><span><a href=\"#3---SQL-a-Langchain\" data-toc-modified-id=\"3---SQL-a-Langchain-3\">3 - SQL a Langchain</a></span></li><li><span><a href=\"#4---Modelo-constructor-de-queries\" data-toc-modified-id=\"4---Modelo-constructor-de-queries-4\">4 - Modelo constructor de queries</a></span></li><li><span><a href=\"#5---Cadena-creadora-de-queries-SQL\" data-toc-modified-id=\"5---Cadena-creadora-de-queries-SQL-5\">5 - Cadena creadora de queries SQL</a></span></li><li><span><a href=\"#6---Creación-de-query-desde-prompt\" data-toc-modified-id=\"6---Creación-de-query-desde-prompt-6\">6 - Creación de query desde prompt</a></span></li><li><span><a href=\"#7---Ejecución-de-la-query\" data-toc-modified-id=\"7---Ejecución-de-la-query-7\">7 - Ejecución de la query</a></span></li><li><span><a href=\"#8---Modelo-de-respuesta-final\" data-toc-modified-id=\"8---Modelo-de-respuesta-final-8\">8 - Modelo de respuesta final</a></span></li><li><span><a href=\"#9---Código-completo\" data-toc-modified-id=\"9---Código-completo-9\">9 - Código completo</a></span></li></ul></div>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "952a9315",
   "metadata": {},
   "source": [
    "## 1 - OpenAI Api Key\n",
    "\n",
    "Para llevar a cabo este proyecto, necesitaremos una API KEY de OpenAI para utilizar el modelo GPT-4 Turbo. Esta API KEY se puede obtener en https://platform.openai.com/api-keys. Solo se muestra una vez, por lo que debe guardarse en el momento en que se obtiene. Por supuesto, necesitaremos crear una cuenta para obtenerla.\n",
    "\n",
    "Guardamos la API KEY en un archivo `.env` para cargarla con la biblioteca dotenv y usarla como una variable de entorno. Este archivo se agrega a `.gitignore` para asegurarnos de que no pueda verse si subimos el código a GitHub, por ejemplo."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "3dca914c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "e609247e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# carga de la api key desde dotenv\n",
    "\n",
    "import os                           \n",
    "from dotenv import load_dotenv     \n",
    "\n",
    "\n",
    "load_dotenv()\n",
    "\n",
    "\n",
    "OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')  # 'sk-.....'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f109a550",
   "metadata": {},
   "source": [
    "## 2 - Conexión a SQL\n",
    "\n",
    "Vamos a conectarnos a MySQL a través de SQLAlchemy utilizando un string de conexión."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2c1bad91",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, text"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "818dcfb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# string de conexion\n",
    "\n",
    "URI = os.getenv('URI') "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc0ec85e",
   "metadata": {},
   "source": [
    "Dependiendo del servidor o del lenguaje utilizado, MySQL o PostGres por ejemplo, la string de conexión cambiará:\n",
    "\n",
    "En MySQL tiene esta forma:\n",
    "\n",
    "`mysql+pymysql://root:password@localhost:3306/sakila`\n",
    "\n",
    "En AWS tiene esta forma:\n",
    "\n",
    "`postgresql://usuario:password@database.us-west-2.rds.amazonaws.com:5432/nombre_db`\n",
    "\n",
    "`mysql://usuario:password@database.us-west-2.rds.amazonaws.com:3306/nombre_db`\n",
    "\n",
    "\n",
    "En SnowFlake tiene esta forma:\n",
    "\n",
    "```python\n",
    "# servidor y credenciales del usuario\n",
    "\n",
    "snowflake_account = os.getenv('snowflake_account')\n",
    "\n",
    "username = os.getenv('username')\n",
    "\n",
    "password = os.getenv('password')\n",
    "\n",
    "database = os.getenv('database')\n",
    "\n",
    "schema = os.getenv('schema')\n",
    "\n",
    "warehouse = os.getenv('warehouse')\n",
    "\n",
    "role = os.getenv('role')\n",
    "\n",
    "\n",
    "URI = f'snowflake://{username}:{password}@{snowflake_account}/{database}/{schema}?warehouse={warehouse}&role={role}'\n",
    "    \n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "190dbd6e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# conexion a SQL \n",
    "\n",
    "cursor = create_engine(URI).connect()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d2dc9092",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['actor',\n",
       " 'address',\n",
       " 'adress2',\n",
       " 'category',\n",
       " 'city',\n",
       " 'country',\n",
       " 'customer',\n",
       " 'film',\n",
       " 'film_actor',\n",
       " 'film_category',\n",
       " 'film_text',\n",
       " 'inventory',\n",
       " 'language',\n",
       " 'payment',\n",
       " 'rental',\n",
       " 'staff',\n",
       " 'staff2',\n",
       " 'store']"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# tablas de la base de datos\n",
    "\n",
    "tablas = cursor.execute(text('show tables;')).all()\n",
    "\n",
    "tablas = [e[0] for e in tablas]\n",
    "\n",
    "tablas"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75688b28",
   "metadata": {},
   "source": [
    "Podemos seleccionar todas las tablas o solamente las tablas que queremos usar para nuestras consultas, no es necesario que el chat conozca toda la estructura de la base de datos, aunque esto le puede dar una mejor compresión. También, dependiendo del modelo y del tamaño de la estructura, el contexto puede ser demasiado grande para que el modelo lo admita."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a31d8f29",
   "metadata": {},
   "source": [
    "## 3 - SQL a Langchain"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9d76b160",
   "metadata": {},
   "source": [
    "Importamos `SQLDatabase` de LangChain para conectarnos a SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e40dff3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain import SQLDatabase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1d9796fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# conexion a langchain con todas las tablas y la primera fila de cada una\n",
    "\n",
    "db = SQLDatabase.from_uri(URI,\n",
    "                          sample_rows_in_table_info=1, \n",
    "                          include_tables=tablas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "34e07afc",
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "CREATE TABLE actor (\n",
      "\tactor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tfirst_name VARCHAR(45) NOT NULL, \n",
      "\tlast_name VARCHAR(45) NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (actor_id)\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from actor table:\n",
      "actor_id\tfirst_name\tlast_name\tlast_update\n",
      "1\tPENELOPE\tGUINESS\t2006-02-15 04:34:33\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE address (\n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\taddress VARCHAR(50) NOT NULL, \n",
      "\taddress2 VARCHAR(50), \n",
      "\tdistrict VARCHAR(20) NOT NULL, \n",
      "\tcity_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tpostal_code VARCHAR(10), \n",
      "\tphone VARCHAR(20) NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (address_id), \n",
      "\tCONSTRAINT fk_address_city FOREIGN KEY(city_id) REFERENCES city (city_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from address table:\n",
      "address_id\taddress\taddress2\tdistrict\tcity_id\tpostal_code\tphone\tlast_update\n",
      "1\t47 MySakila Drive\tNone\tAlberta\t300\t\t\t2014-09-25 22:30:27\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE adress2 (\n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL DEFAULT '0', \n",
      "\taddress VARCHAR(50) NOT NULL, \n",
      "\taddress2 VARCHAR(50), \n",
      "\tdistrict VARCHAR(20) NOT NULL, \n",
      "\tcity_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tpostal_code VARCHAR(10), \n",
      "\tphone VARCHAR(20) NOT NULL, \n",
      "\tlocation VARCHAR(1) NOT NULL DEFAULT '', \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from adress2 table:\n",
      "address_id\taddress\taddress2\tdistrict\tcity_id\tpostal_code\tphone\tlocation\tlast_update\n",
      "1\t47 MySakila Drive\tNone\tAlberta\t300\t\t\tA\t2014-09-25 22:30:27\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE category (\n",
      "\tcategory_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tname VARCHAR(25) NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (category_id)\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from category table:\n",
      "category_id\tname\tlast_update\n",
      "1\tAction\t2006-02-15 04:46:27\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE city (\n",
      "\tcity_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tcity VARCHAR(50) NOT NULL, \n",
      "\tcountry_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (city_id), \n",
      "\tCONSTRAINT fk_city_country FOREIGN KEY(country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from city table:\n",
      "city_id\tcity\tcountry_id\tlast_update\n",
      "1\tA Coruña (La Coruña)\t87\t2006-02-15 04:45:25\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE country (\n",
      "\tcountry_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tcountry VARCHAR(50) NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (country_id)\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from country table:\n",
      "country_id\tcountry\tlast_update\n",
      "1\tAfghanistan\t2006-02-15 04:44:00\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE customer (\n",
      "\tcustomer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tstore_id TINYINT UNSIGNED NOT NULL, \n",
      "\tfirst_name VARCHAR(45) NOT NULL, \n",
      "\tlast_name VARCHAR(45) NOT NULL, \n",
      "\temail VARCHAR(50), \n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tactive TINYINT(1) NOT NULL DEFAULT '1', \n",
      "\tcreate_date DATETIME NOT NULL, \n",
      "\tlast_update TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (customer_id), \n",
      "\tCONSTRAINT fk_customer_address FOREIGN KEY(address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_customer_store FOREIGN KEY(store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from customer table:\n",
      "customer_id\tstore_id\tfirst_name\tlast_name\temail\taddress_id\tactive\tcreate_date\tlast_update\n",
      "1\t1\tMARY\tSMITH\tMARY.SMITH@sakilacustomer.org\t5\t1\t2006-02-14 22:04:36\t2006-02-15 04:57:20\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE film (\n",
      "\tfilm_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\ttitle VARCHAR(128) NOT NULL, \n",
      "\tdescription TEXT, \n",
      "\trelease_year YEAR, \n",
      "\tlanguage_id TINYINT UNSIGNED NOT NULL, \n",
      "\toriginal_language_id TINYINT UNSIGNED, \n",
      "\trental_duration TINYINT UNSIGNED NOT NULL DEFAULT '3', \n",
      "\trental_rate DECIMAL(4, 2) NOT NULL DEFAULT '4.99', \n",
      "\tlength SMALLINT UNSIGNED, \n",
      "\treplacement_cost DECIMAL(5, 2) NOT NULL DEFAULT '19.99', \n",
      "\trating ENUM('G','PG','PG-13','R','NC-17') DEFAULT 'G', \n",
      "\tspecial_features SET('Trailers','Commentaries','Deleted Scenes','Behind the Scenes'), \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (film_id), \n",
      "\tCONSTRAINT fk_film_language FOREIGN KEY(language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_film_language_original FOREIGN KEY(original_language_id) REFERENCES language (language_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from film table:\n",
      "film_id\ttitle\tdescription\trelease_year\tlanguage_id\toriginal_language_id\trental_duration\trental_rate\tlength\treplacement_cost\trating\tspecial_features\tlast_update\n",
      "1\tACADEMY DINOSAUR\tA Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies\t2006\t1\tNone\t6\t0.99\t86\t20.99\tPG\t{'Behind the Scenes', 'Deleted Scenes'}\t2006-02-15 05:03:42\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE film_actor (\n",
      "\tactor_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tfilm_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (actor_id, film_id), \n",
      "\tCONSTRAINT fk_film_actor_actor FOREIGN KEY(actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_film_actor_film FOREIGN KEY(film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from film_actor table:\n",
      "actor_id\tfilm_id\tlast_update\n",
      "1\t1\t2006-02-15 05:05:03\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE film_category (\n",
      "\tfilm_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tcategory_id TINYINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (film_id, category_id), \n",
      "\tCONSTRAINT fk_film_category_category FOREIGN KEY(category_id) REFERENCES category (category_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_film_category_film FOREIGN KEY(film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from film_category table:\n",
      "film_id\tcategory_id\tlast_update\n",
      "1\t6\t2006-02-15 05:07:09\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE film_text (\n",
      "\tfilm_id SMALLINT UNSIGNED NOT NULL, \n",
      "\ttitle VARCHAR(255) NOT NULL, \n",
      "\tdescription TEXT, \n",
      "\tPRIMARY KEY (film_id)\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from film_text table:\n",
      "film_id\ttitle\tdescription\n",
      "1\tACADEMY DINOSAUR\tA Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE inventory (\n",
      "\tinventory_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tfilm_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tstore_id TINYINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (inventory_id), \n",
      "\tCONSTRAINT fk_inventory_film FOREIGN KEY(film_id) REFERENCES film (film_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_inventory_store FOREIGN KEY(store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from inventory table:\n",
      "inventory_id\tfilm_id\tstore_id\tlast_update\n",
      "1\t1\t1\t2006-02-15 05:09:17\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE language (\n",
      "\tlanguage_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tname CHAR(20) NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (language_id)\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from language table:\n",
      "language_id\tname\tlast_update\n",
      "1\tEnglish\t2006-02-15 05:02:19\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE payment (\n",
      "\tpayment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tcustomer_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tstaff_id TINYINT UNSIGNED NOT NULL, \n",
      "\trental_id INTEGER, \n",
      "\tamount DECIMAL(5, 2) NOT NULL, \n",
      "\tpayment_date DATETIME NOT NULL, \n",
      "\tlast_update TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (payment_id), \n",
      "\tCONSTRAINT fk_payment_customer FOREIGN KEY(customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_payment_rental FOREIGN KEY(rental_id) REFERENCES rental (rental_id) ON DELETE SET NULL ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_payment_staff FOREIGN KEY(staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from payment table:\n",
      "payment_id\tcustomer_id\tstaff_id\trental_id\tamount\tpayment_date\tlast_update\n",
      "1\t1\t1\t76\t2.99\t2005-05-25 11:30:37\t2006-02-15 22:12:30\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE rental (\n",
      "\trental_id INTEGER NOT NULL AUTO_INCREMENT, \n",
      "\trental_date DATETIME NOT NULL, \n",
      "\tinventory_id MEDIUMINT UNSIGNED NOT NULL, \n",
      "\tcustomer_id SMALLINT UNSIGNED NOT NULL, \n",
      "\treturn_date DATETIME, \n",
      "\tstaff_id TINYINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (rental_id), \n",
      "\tCONSTRAINT fk_rental_customer FOREIGN KEY(customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_rental_inventory FOREIGN KEY(inventory_id) REFERENCES inventory (inventory_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_rental_staff FOREIGN KEY(staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from rental table:\n",
      "rental_id\trental_date\tinventory_id\tcustomer_id\treturn_date\tstaff_id\tlast_update\n",
      "1\t2005-05-24 22:53:30\t367\t130\t2005-05-26 22:04:30\t1\t2006-02-15 21:30:53\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE staff (\n",
      "\tstaff_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tfirst_name VARCHAR(45) NOT NULL, \n",
      "\tlast_name VARCHAR(45) NOT NULL, \n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tpicture BLOB, \n",
      "\temail VARCHAR(50), \n",
      "\tstore_id TINYINT UNSIGNED NOT NULL, \n",
      "\tactive TINYINT(1) NOT NULL DEFAULT '1', \n",
      "\tusername VARCHAR(16) NOT NULL, \n",
      "\tpassword VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (staff_id), \n",
      "\tCONSTRAINT fk_staff_address FOREIGN KEY(address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_staff_store FOREIGN KEY(store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from staff table:\n",
      "staff_id\tfirst_name\tlast_name\taddress_id\tpicture\temail\tstore_id\tactive\tusername\tpassword\tlast_update\n",
      "1\tMike\tHillyer\t3\tb'\\x89PNG\\r\\n\\x1a\\n\\x00\\x00\\x00\\rIHDR\\x00\\x00\\x00y\\x00\\x00\\x00u\\x08\\x02\\x00\\x00\\x00\\xe5Z\\xd9e\\x00\\x0\tMike.Hillyer@sakilastaff.com\t1\t1\tMike\t8cb2237d0679ca88db6464eac60da96345513964\t2006-02-15 03:57:16\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE staff2 (\n",
      "\tstaff_id TINYINT UNSIGNED NOT NULL DEFAULT '0', \n",
      "\tfirst_name VARCHAR(45) NOT NULL, \n",
      "\tlast_name VARCHAR(45) NOT NULL, \n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tpicture VARCHAR(1) NOT NULL DEFAULT '', \n",
      "\temail VARCHAR(50), \n",
      "\tstore_id TINYINT UNSIGNED NOT NULL, \n",
      "\tactive TINYINT(1) NOT NULL DEFAULT '1', \n",
      "\tusername VARCHAR(16) NOT NULL, \n",
      "\tpassword VARCHAR(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from staff2 table:\n",
      "staff_id\tfirst_name\tlast_name\taddress_id\tpicture\temail\tstore_id\tactive\tusername\tpassword\tlast_update\n",
      "1\tMike\tHillyer\t3\tA\tMike.Hillyer@sakilastaff.com\t1\t1\tMike\t8cb2237d0679ca88db6464eac60da96345513964\t2006-02-15 03:57:16\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE store (\n",
      "\tstore_id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, \n",
      "\tmanager_staff_id TINYINT UNSIGNED NOT NULL, \n",
      "\taddress_id SMALLINT UNSIGNED NOT NULL, \n",
      "\tlast_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, \n",
      "\tPRIMARY KEY (store_id), \n",
      "\tCONSTRAINT fk_store_address FOREIGN KEY(address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, \n",
      "\tCONSTRAINT fk_store_staff FOREIGN KEY(manager_staff_id) REFERENCES staff (staff_id) ON DELETE RESTRICT ON UPDATE CASCADE\n",
      ")DEFAULT CHARSET=utf8mb4 ENGINE=InnoDB COLLATE utf8mb4_0900_ai_ci\n",
      "\n",
      "/*\n",
      "1 rows from store table:\n",
      "store_id\tmanager_staff_id\taddress_id\tlast_update\n",
      "1\t1\t1\t2006-02-15 04:57:12\n",
      "*/\n"
     ]
    }
   ],
   "source": [
    "# estructura completa que se usara como contexto\n",
    "\n",
    "print(db.table_info)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d624c7e",
   "metadata": {},
   "source": [
    "## 4 - Modelo constructor de queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ceba2f95",
   "metadata": {},
   "source": [
    "Usamos un modelo de OpenAI como LLM de entrada. Una temperatura = 0 hace que las respuestas del modelo sean más deterministas y repetitivas, tendiendo a seleccionar la siguiente palabra más probable según el contexto anterior, lo cual es mejor para la generación de consultas SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "980d3ba0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# importamos el modelo\n",
    "\n",
    "from langchain_openai import ChatOpenAI\n",
    "\n",
    "input_model = ChatOpenAI(model='gpt-4.1', temperature=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "b6c12f72",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'¡Hola! ¿En qué puedo ayudarte hoy? 😊'"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# prueba de uso\n",
    "\n",
    "input_model.invoke('hola').content"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8582bf9c",
   "metadata": {},
   "source": [
    "## 5 - Cadena creadora de queries SQL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "95a57daf",
   "metadata": {},
   "source": [
    "Importamos la cadena predefinida que tiene LangChain para test2sql."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "889f92cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.chains import create_sql_query_chain"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "dae1238c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "RunnableAssign(mapper={\n",
       "  input: RunnableLambda(...),\n",
       "  table_info: RunnableLambda(...)\n",
       "})\n",
       "| RunnableLambda(lambda x: {k: v for (k, v) in x.items() if k not in ('question', 'table_names_to_use')})\n",
       "| PromptTemplate(input_variables=['input', 'table_info'], input_types={}, partial_variables={'top_k': '5'}, template='You are a MySQL expert. Given an input question, first create a syntactically correct MySQL query to run, then look at the results of the query and return the answer to the input question.\\nUnless the user specifies in the question a specific number of examples to obtain, query for at most {top_k} results using the LIMIT clause as per MySQL. You can order the results to return the most informative data in the database.\\nNever query for all columns from a table. You must query only the columns that are needed to answer the question. Wrap each column name in backticks (`) to denote them as delimited identifiers.\\nPay attention to use only the column names you can see in the tables below. Be careful to not query for columns that do not exist. Also, pay attention to which column is in which table.\\nPay attention to use CURDATE() function to get the current date, if the question involves \"today\".\\n\\nUse the following format:\\n\\nQuestion: Question here\\nSQLQuery: SQL Query to run\\nSQLResult: Result of the SQLQuery\\nAnswer: Final answer here\\n\\nOnly use the following tables:\\n{table_info}\\n\\nQuestion: {input}')\n",
       "| RunnableBinding(bound=ChatOpenAI(client=<openai.resources.chat.completions.completions.Completions object at 0x115fb4280>, async_client=<openai.resources.chat.completions.completions.AsyncCompletions object at 0x115fc2eb0>, root_client=<openai.OpenAI object at 0x106188fd0>, root_async_client=<openai.AsyncOpenAI object at 0x115fb4340>, model_name='gpt-4.1', temperature=0.0, model_kwargs={}, openai_api_key=SecretStr('**********')), kwargs={'stop': ['\\nSQLResult:']}, config={}, config_factories=[])\n",
       "| StrOutputParser()\n",
       "| RunnableLambda(_strip)"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# cadena por defecto\n",
    "\n",
    "database_chain = create_sql_query_chain(input_model, db)\n",
    "\n",
    "database_chain"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d16ab31",
   "metadata": {},
   "source": [
    "Aqui podemos cambiar el prompt que viene por defecto en la cadena para mejorar la query de salida si fuera necesario."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f42ded76",
   "metadata": {},
   "outputs": [],
   "source": [
    "# prompt custom\n",
    "\n",
    "from langchain_core.prompts import PromptTemplate\n",
    "\n",
    "template = '''\n",
    "\n",
    "Eres un experto en {dialect}. Dada una pregunta de entrada:\n",
    "\n",
    "1. Primero, crea una consulta en {dialect} que sea sintácticamente correcta para ejecutar.  \n",
    "2. A menos que el usuario especifique en la pregunta un número específico de ejemplos a obtener, \n",
    "no utilices la cláusula `LIMIT`. Si se pide limitar sin un número específico, utiliza las primera {top_k} filas.  \n",
    "3. Nunca consultes todas las columnas de una tabla; solo debes seleccionar las columnas \n",
    "necesarias para responder la pregunta.  \n",
    "4. Encierra cada nombre de columna con backticks (`) para identificarlos como identificadores delimitados.  \n",
    "5. Usa solo los nombres de columna visibles en las tablas a continuación. No consultes columnas que no existan. \n",
    "Asegúrate de saber en qué tabla se encuentra cada columna.  \n",
    "6. Usa la función `CURDATE()` para obtener la fecha actual si la pregunta involucra \"hoy\".  \n",
    "7. Solo usa sentencias `SELECT` para consultar datos.  \n",
    "8. La información de las tablas es la siguiente:\n",
    "{table_info}  \n",
    "\n",
    "### Pregunta  \n",
    "{input}  \n",
    "\n",
    "### Formato de salida  \n",
    "```sql  \n",
    "consulta a ejecutar  \n",
    "```  \n",
    "\n",
    "Tu respuesta debe ser únicamente la consulta en {dialect}. \n",
    "'''\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "sql_prompt = PromptTemplate(input_variables=['input', 'table_info', 'top_k', 'dialect'],\n",
    "                            template=template)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "7c2f4687",
   "metadata": {},
   "outputs": [],
   "source": [
    "database_chain = create_sql_query_chain(input_model, db, prompt=sql_prompt)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4414fd64",
   "metadata": {},
   "source": [
    "## 6 - Creación de query desde prompt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46dd2a71",
   "metadata": {},
   "source": [
    "Ahora, vamos a crear la query de SQL desde castellano. El modelo funciona mejor en inglés, por lo que primero vamos a traducir nuestra consulta al inglés para luego pedirle al modelo que cree la query de SQL."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "6745a426",
   "metadata": {},
   "outputs": [],
   "source": [
    "# nuestra pregunta\n",
    "\n",
    "prompt = '¿Qué actores tienen de primer nombre SCARLETT?'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "f25cc976",
   "metadata": {},
   "outputs": [],
   "source": [
    "# llamada al modelo creador de queries\n",
    "\n",
    "sql_query = database_chain.invoke({'question': prompt})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "8a602b74",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\nSELECT actor_id, first_name, last_name\\nFROM actor\\nWHERE first_name = 'SCARLETT';\\n\""
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# query SQL creada\n",
    "\n",
    "sql_query = sql_query.split('```sql')[1].replace('`', '')\n",
    "\n",
    "sql_query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3140aad4",
   "metadata": {},
   "source": [
    "## 7 - Ejecución de la query"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a20779f7",
   "metadata": {},
   "source": [
    "Una vez obtenida la query, debemos ejecutarla en el servidor de SQL. Podemos hacer esto directamente con SQLAlchemy. Este es el paso crítico, puesto que si la query no está bien escrita la ejecución fallará. De ocurrir esto podríamos cambiar el prompt del modelo generador de queries para intentar que la query sea operativa."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "8cf12f49",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(81, 'SCARLETT', 'DAMON'), (124, 'SCARLETT', 'BENING')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# respuesta de la query que usaremos como contexto\n",
    "\n",
    "contexto = cursor.execute(text(sql_query)).all()\n",
    "\n",
    "contexto"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "12343246",
   "metadata": {},
   "source": [
    "## 8 - Modelo de respuesta final"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06f77db0",
   "metadata": {},
   "source": [
    "Una vez obtenida la respuesta del servidor SQL, pasamos al modelo final la tabla como contexto y le damos nuestra pregunta otra vez para que la respuesta sea en castellano."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "93aa489d",
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_openai.chat_models import ChatOpenAI   \n",
    "\n",
    "output_model = ChatOpenAI(model='gpt-4.1')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "0405ea68",
   "metadata": {},
   "outputs": [],
   "source": [
    "# le damos la pregunta original y el contexto extraido desde SQL\n",
    "\n",
    "final_prompt = f'''Dados el siguiente contexto y query, responde la pregunta: \n",
    "                    \n",
    "                    contexto: {contexto}, \n",
    "                    \n",
    "                    query: {sql_query},\n",
    "                    \n",
    "                    pregunta: {prompt}\n",
    "                    \n",
    "                    No hables del contexto ni de la query.\n",
    "                    Devuelve la respuesta lo mas extensa posible.\n",
    "                    \n",
    "                    '''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "297c309e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Los actores cuyo primer nombre es SCARLETT son los siguientes:\n",
      "\n",
      "1. Un actor con el identificador 81, cuyo nombre completo es SCARLETT DAMON. Esto significa que su primer nombre es SCARLETT y su apellido es DAMON. \n",
      "\n",
      "2. Otro actor con el identificador 124, cuyo nombre completo es SCARLETT BENING. En este caso, el primer nombre también es SCARLETT y el apellido es BENING.\n",
      "\n",
      "En resumen, existen dos actores que tienen como primer nombre SCARLETT. El primero de ellos tiene DAMON como apellido y el segundo BENING. Ambos comparten el mismo primer nombre pero tienen apellidos diferentes y sus identificadores únicos (ID de actor) también son distintos: uno es el número 81 y el otro el 124. Esta información permite identificar de manera precisa a los actores que tienen SCARLETT como nombre de pila.\n"
     ]
    }
   ],
   "source": [
    "respuesta_final = output_model.invoke(final_prompt).content\n",
    "\n",
    "print(respuesta_final)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79443fbf",
   "metadata": {},
   "source": [
    "## 9 - Código completo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "35a298a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Los actores que tienen como apellido JOHANSSON son:\n",
      "\n",
      "MATTHEW JOHANSSON, quien tiene el identificador de actor 8.  \n",
      "RAY JOHANSSON, con el identificador de actor 64.  \n",
      "ALBERT JOHANSSON, cuyo identificador de actor es 146.  \n",
      "\n",
      "Cada uno de ellos comparte el apellido JOHANSSON, aunque sus nombres y números de identificación son distintos. Por un lado, MATTHEW JOHANSSON es uno de los actores con ese apellido y ocupa el primer lugar en la lista con el actor_id 8. Por otro, RAY JOHANSSON también lleva el mismo apellido y está identificado con el actor_id 64. Finalmente, ALBERT JOHANSSON completa la lista, con el actor_id 146.\n",
      "\n",
      "En conjunto, estos tres actores (MATTHEW, RAY y ALBERT) comparten el apellido JOHANSSON y están registrados con los identificadores 8, 64 y 146 respectivamente.\n"
     ]
    }
   ],
   "source": [
    "# librerias   \n",
    "\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')                     # para quitar avisos\n",
    "\n",
    "from sqlalchemy import create_engine, text            # conexion SQL y text para queries\n",
    "\n",
    "from langchain import SQLDatabase                     # conexion SQL a LangChain\n",
    "from langchain_core.prompts import PromptTemplate     # creacion de prompts\n",
    "from langchain.chains import create_sql_query_chain   # cadana de creacion de queries SQL\n",
    "from langchain_openai.chat_models import ChatOpenAI   # modelo chat OpenAI\n",
    "\n",
    "\n",
    "import os                                             # libreria del sistema\n",
    "from dotenv import load_dotenv                        # carga de variables de entorno\n",
    " \n",
    "\n",
    "# variables de entorno\n",
    "load_dotenv()\n",
    "\n",
    "\n",
    "# api key OpenAI\n",
    "OPENAI_API_KEY = os.getenv('OPENAI_API_KEY')\n",
    "\n",
    "\n",
    "# string de conexion servidor SQL\n",
    "URI = os.getenv('URI')\n",
    "\n",
    "\n",
    "\n",
    "# prompt inicial\n",
    "input_model = ChatOpenAI(model='gpt-4.1', temperature=0)\n",
    "\n",
    "prompt = '¿Qué actores tienen como apellido JOHANSSON?'\n",
    "\n",
    "\n",
    "\n",
    "# conexion a base de datos\n",
    "cursor = create_engine(URI).connect()\n",
    "\n",
    "tablas = cursor.execute(text('show tables;')).all()\n",
    "tablas = [e[0] for e in tablas]\n",
    "\n",
    "db = SQLDatabase.from_uri(URI, sample_rows_in_table_info=1, include_tables=tablas)\n",
    "\n",
    "\n",
    "\n",
    "# definion del prompt para generar query SQL\n",
    "template = '''\n",
    "\n",
    "Eres un experto en {dialect}. Dada una pregunta de entrada:\n",
    "\n",
    "1. Primero, crea una consulta en {dialect} que sea sintácticamente correcta para ejecutar.  \n",
    "2. A menos que el usuario especifique en la pregunta un número específico de ejemplos a obtener, \n",
    "no utilices la cláusula `LIMIT`. Si se pide limitar sin un número específico, utiliza las primera {top_k} filas.  \n",
    "3. Nunca consultes todas las columnas de una tabla; solo debes seleccionar las columnas \n",
    "necesarias para responder la pregunta.  \n",
    "4. Encierra cada nombre de columna con backticks (`) para identificarlos como identificadores delimitados.  \n",
    "5. Usa solo los nombres de columna visibles en las tablas a continuación. No consultes columnas que no existan. \n",
    "Asegúrate de saber en qué tabla se encuentra cada columna.  \n",
    "6. Usa la función `CURDATE()` para obtener la fecha actual si la pregunta involucra \"hoy\".  \n",
    "7. Solo usa sentencias `SELECT` para consultar datos.  \n",
    "8. La información de las tablas es la siguiente:\n",
    "{table_info}  \n",
    "\n",
    "### Pregunta  \n",
    "{input}  \n",
    "\n",
    "### Formato de salida  \n",
    "```sql  \n",
    "consulta a ejecutar  \n",
    "```  \n",
    "\n",
    "Tu respuesta debe ser únicamente la consulta en {dialect}. \n",
    "'''\n",
    "\n",
    "\n",
    "sql_prompt = PromptTemplate(input_variables=['input', 'table_info', 'top_k', 'dialect'],\n",
    "                            template=template)\n",
    "\n",
    "\n",
    "# creacion de query SQL\n",
    "database_chain = create_sql_query_chain(input_model, db, prompt=sql_prompt)\n",
    "\n",
    "sql_query = database_chain.invoke({'question': prompt})\n",
    "\n",
    "sql_query = sql_query.split('```sql')[1].replace('`', '')\n",
    "\n",
    "\n",
    "# ejecucion de la query SQL\n",
    "contexto = cursor.execute(text(sql_query)).all()\n",
    "\n",
    "\n",
    "# respuesta final \n",
    "output_model = ChatOpenAI(model='gpt-4.1')\n",
    "\n",
    "final_prompt = f'''Dados el siguiente contexto y query, responde la pregunta: \n",
    "                    \n",
    "                    contexto: {contexto}, \n",
    "                    \n",
    "                    query: {sql_query},\n",
    "                    \n",
    "                    pregunta: {prompt}\n",
    "                    \n",
    "                    No hables del contexto ni de la query.\n",
    "                    Devuelve la respuesta lo mas extensa posible.\n",
    "                    \n",
    "                    '''\n",
    "\n",
    "respuesta = output_model.invoke(final_prompt).content\n",
    "\n",
    "\n",
    "print(respuesta)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b38e21e6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "ia",
   "language": "python",
   "name": "ia"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.21"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": false,
   "sideBar": true,
   "skip_h1_title": true,
   "title_cell": "Tabla de Contenidos",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "254.852px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
